import os
from datetime import datetime, timedelta
import openpyxl
from service.models import PredictionFact, Event
from users.models import User, SpecializationType
from django.conf import settings
from django.db.models import F
from openpyxl.styles import Alignment


class PredictionReportGenerator:
    def __init__(self, year, month):
        self.year = year
        self.month = month
        self.report_dir = os.path.join(settings.MEDIA_ROOT, 'reports')
        os.makedirs(self.report_dir, exist_ok=True)
        self.filename = os.path.join(self.report_dir, f"prediction_report_{year}_{month}.xlsx")
        self.workbook = openpyxl.Workbook()
        self.worksheet = self.workbook.active
        self.worksheet.title = f"{year}_{month}"
        self.specializations = list(SpecializationType.objects.all())

    def get_headers(self):
        headers = ['Год', 'Номер недели'] + [f"{str(spec)} прогноз" for spec in self.specializations]
        self.worksheet.append(headers)

    def get_prediction_data(self):
        prediction_facts = (
            PredictionFact.objects
            .filter(month__year=self.year, month__month=self.month)
            .select_related('specialization')
            .values('week', 'specialization')
            .annotate(predicted_quantity=F('qtty_predicted'))
            .order_by('week', 'specialization')
        )

        result = {}
        for fact in prediction_facts:
            week = fact['week']
            specialization = fact['specialization']
            predicted_quantity = fact['predicted_quantity']
            if week not in result:
                result[week] = {}
            result[week][specialization] = predicted_quantity

        return result

    def populate_worksheet(self, data):
        for week, predictions in data.items():
            row = [self.year, week]
            for specialization in self.specializations:
                predicted_quantity = predictions.get(specialization.id, '')
                row.append(predicted_quantity)
            self.worksheet.append(row)

    def save_report(self):
        self.workbook.save(self.filename)
        return self.filename

    def generate_report(self):
        self.get_headers()
        prediction_data = self.get_prediction_data()
        self.populate_worksheet(prediction_data)
        return self.save_report()


class ScheduleReportGenerator:
    def __init__(self, year, month):
        self.year = year
        self.month = month
        self.report_dir = os.path.join(settings.MEDIA_ROOT, 'reports')
        os.makedirs(self.report_dir, exist_ok=True)
        self.filename = os.path.join(self.report_dir, f"schedule_report_{year}_{month}.xlsx")
        self.workbook = openpyxl.Workbook()
        self.worksheet = self.workbook.active
        self.worksheet.title = f"{year}_{month}"

    def get_headers(self):
        headers = ["ФИО", "Модальность", "Дополнительные модальности", "Ставка", "Таб. №", "Время"]
        self.worksheet.append(headers)
        self.worksheet.append([None] * len(headers))

        for col in range(1, 7):
            self.worksheet.merge_cells(start_row=1, start_column=col, end_row=2, end_column=col)
            cell = self.worksheet.cell(row=1, column=col)
            cell.alignment = Alignment(horizontal='center', vertical='center')

        unique_days = Event.objects.filter(
            month__year=self.year,
            month__month=self.month,
            kind='work_time'
        ).values_list('day', flat=True).distinct().order_by('day')

        if unique_days:
            self.worksheet.cell(row=1, column=7, value="Числа месяца")
            self.worksheet.merge_cells(start_row=1, start_column=7, end_row=1,
                                       end_column=7 + len(unique_days) - 1)
            self.worksheet.cell(row=1, column=7).alignment = Alignment(horizontal='center', vertical='center')

            for idx, day in enumerate(unique_days):
                col = 7 + idx
                self.worksheet.cell(row=2, column=col, value=day)
                self.worksheet.cell(row=2, column=col).alignment = Alignment(horizontal='center',
                                                                             vertical='center')

        # Добавляем заголовок "Итого месяц"
        total_col = 7 + len(unique_days)
        self.worksheet.cell(row=1, column=total_col, value="Итого за месяц")
        self.worksheet.merge_cells(start_row=1, start_column=total_col, end_row=2, end_column=total_col)
        self.worksheet.cell(row=1, column=total_col).alignment = Alignment(horizontal='center',
                                                                           vertical='center')

        # Добавляем заголовки для пустых столбцов
        empty_headers = ["Норма часов по графику", "Норма часов за полный месяц", "Дата", "Подпись"]
        for idx, header in enumerate(empty_headers):
            col = total_col + 1 + idx
            self.worksheet.cell(row=1, column=col, value=header)
            self.worksheet.merge_cells(start_row=1, start_column=col, end_row=2, end_column=col)
            self.worksheet.cell(row=1, column=col).alignment = Alignment(horizontal='center', vertical='center')

    def get_user_data(self):
        users = User.objects.all()
        data = []
        for user in users:
            fio = user.fio
            modality = user.main_specialization.specialization if user.main_specialization else ''
            additional_modalities = ', '.join(
                [spec.specialization for spec in user.additional_specializations.all()])
            employment_fraction = user.employment_fraction
            data.append([user.id, fio, modality, additional_modalities, employment_fraction, '', ''])
        return data

    def populate_worksheet(self, data):
        row_index = 3
        unique_days = Event.objects.filter(
            month__year=self.year,
            month__month=self.month,
            kind='work_time'
        ).values_list('day', flat=True).distinct().order_by('day')

        for user_data in data:
            user_id, fio, modality, additional_modalities, employment_fraction, _, _ = user_data
            total_hours = timedelta()

            for i in range(4):
                self.worksheet.append(
                    [fio, modality, additional_modalities, employment_fraction, '', ''] if i == 0 else [
                                                                                                           None] * 6)

            for col in range(1, 6):
                self.worksheet.merge_cells(start_row=row_index, start_column=col, end_row=row_index + 3,
                                           end_column=col)

            self.worksheet.cell(row=row_index, column=6, value='с')
            self.worksheet.cell(row=row_index + 1, column=6, value='до')
            self.worksheet.cell(row=row_index + 2, column=6, value='перерыв')
            self.worksheet.cell(row=row_index + 3, column=6, value='отраб.')

            events = Event.objects.filter(
                user_id=user_id,
                month__year=self.year,
                month__month=self.month,
                kind='work_time'
            )

            for idx, day in enumerate(unique_days):
                col = 7 + idx
                event_for_day = events.filter(day=day).first()
                if event_for_day:
                    start_time = event_for_day.start_time
                    end_time = event_for_day.end_time
                    work_hours = datetime.combine(datetime.min, end_time) - datetime.combine(datetime.min,
                                                                                             start_time)

                    if work_hours >= timedelta(hours=8):
                        break_time = timedelta(hours=1)
                    else:
                        break_time = timedelta(minutes=30)

                    end_time = (datetime.combine(datetime.min, end_time) + break_time).time()
                    total_hours += work_hours

                    self.worksheet.cell(row=row_index, column=col, value=start_time.strftime('%H:%M'))
                    self.worksheet.cell(row=row_index + 1, column=col, value=end_time.strftime('%H:%M'))
                    self.worksheet.cell(row=row_index + 2, column=col,
                                        value=(datetime.min + break_time).strftime('%H:%M'))
                    self.worksheet.cell(row=row_index + 3, column=col,
                                        value=(datetime.min + work_hours).strftime('%H:%M'))

            for i in range(4):
                for col in range(1, 7 + len(unique_days)):
                    cell = self.worksheet.cell(row=row_index + i, column=col)
                    cell.alignment = Alignment(horizontal='center', vertical='center')

            total_col = 7 + len(unique_days)

            total_hours_float = round(total_hours.total_seconds() / 3600, 1)
            self.worksheet.cell(row=row_index, column=total_col, value=total_hours_float)
            self.worksheet.merge_cells(start_row=row_index, start_column=total_col, end_row=row_index + 3,
                                       end_column=total_col)
            self.worksheet.cell(row=row_index, column=total_col).alignment = Alignment(horizontal='center',
                                                                                       vertical='center')
            # Добавление пустых ячеек для новых столбцов
            for idx in range(4):
                col = total_col + 1 + idx
                self.worksheet.merge_cells(start_row=row_index, start_column=col, end_row=row_index + 3,
                                           end_column=col)
                self.worksheet.cell(row=row_index, column=col).alignment = Alignment(horizontal='center',
                                                                                     vertical='center')

            row_index += 4

    def save_report(self):
        self.workbook.save(self.filename)
        return self.filename

    def generate_report(self):
        self.get_headers()
        user_data = self.get_user_data()
        self.populate_worksheet(user_data)
        return self.save_report()
